package net.oschina.arvin.dbjava.util;

import net.oschina.arvin.dbjava.model.Column;
import net.oschina.arvin.dbjava.model.Table;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * <pre>
 * POM 依赖：
 *  MySQL 驱动
 *  <dependency>
 *      <groupId>mysql</groupId>
 *      <artifactId>mysql-connector-java</artifactId>
 *      <version>5.1.29</version>
 *  </dependency>
 *
 *
 *  Oracle 驱动
 *  <dependency>
 *      <groupId>org.oracle</groupId>
 *      <artifactId>ojdbc11</artifactId>
 *      <version>11.2.0.2.4</version>
 *  </dependency>
 *
 *
 *  注： Oracle的驱动包，在Maven的中央没有，需要自己下载驱动包，然后安装到本地仓库或是安装到私有仓库才能引用
 *      驱动包下载地址： http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html
 *      或百度直接搜索： oracle driver download
 *      下载界面选择一个合适版本下载，如 ojdbc6.jar
 *  安装到本地仓库
 *      我们一般按照Oracle版本命名，如下载到的ojdbc.jar, 下载界面可能是Oracle Database 11g Release 2 (11.2.0.4) JDBC Drivers
 *      那么我们就定义好artifactId = ojdbc11, version=11.2.0.4, groupId = org.oracle
 *      mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.4 -Dpackaging=jar -Dfile=ojdbc6.jar
 *  安装到私有仓库
 *      mvn deploy:deploy-file -DgroupId=com.oracle -DartifactId=ojdbc11 -Dversion=11.2.0.1.4 -Dpackaging=jar -Dfile=ojdbc6.jar -Durl={private.repository.url} -DrepositoryId=thirdparty
 *      其中-Durl即为私有仓库地址，如： http://124.232.150.208:8181/nexus/content/repositories/thirdparty/
 *      注意，你需要在全局的settings.xml中配置好访问这个仓库地址的帐号和密码，才能发布上去
 *      当然，你也可以直接使用Nexus私有仓库后台的管理界面实现私有库的添加
 *
 * 使用：
 *  MySQL:  DBUtils.mysqlBuilder(schema); // 参数不能为空
 *  Oracle: DBUtils.oracleBuilder(instance); // 如果为空，默认就是XE
 *
 *  DBUtils jdbc = DBUtils.builder(schema) // 建造者模式, 参数为要连接的实例
 *                        .driver() // 默认MySQL驱动。定义驱动, mysql = DBUtils.DRIVER_MYSQL = com.mysql.jdbc.Driver, DBUtils.DRIVER_ORACLE = oracle.jdbc.driver.OracleDriver
 *                        .host() // 数据库主机, 默认localhost
 *                        .port() // 数据库主机端口， 默认3306
 *                        .username() // 数据库用户名，默认root
 *                        .password() // 数据库密码，默认admin
 *                        .underlineField() // 数据库字段规则，是否使用下划线分割，默认是true， false表示字段使用驼峰命名
 *                        .underlineTable() // 数据库表名规则，是否使用下划线分割，默认是true， false表示使用驼峰命名
 *                        .stdlog() // 是否开启System.out日志，默认开启
 *                        .ignoredUnknownField() // 查询的时候，返回对象时，是否忽略未知属性的赋值，默认是true，如果为false，那么当出现位置属性的时候，将抛出异常
 *
 * </pre>
 *
 * @author 夏集球
 * @time 2016/9/23$ 11:52$
 */
@SuppressWarnings({"unused"})
public class DBUtils {
    /**
     * MySQL驱动
     */
    public static final String DRIVER_MYSQL = "com.mysql.jdbc.Driver";
    /**
     * Oracle驱动
     */
    public static final String DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
    /**
     * MySQL JDBC_URL
     */
    public static final String JDBC_URL_MYSQL = "jdbc:mysql://{host}:{port}/{schema}?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE";
    /**
     * Oracle JDBC_URL
     */
    public static final String JDBC_URL_ORACLE = "jdbc:oracle:thin:@{host}:{port}:{schema}";

    private final String driver;
    private final String jdbcUrl;
    private final String username;
    private final String password;
    /**
     * 数据库字段是否使用下划线分割
     */
    private final boolean underlineFiled;
    /**
     * 数据库表是否使用下划线分割
     */
    private final boolean underlineTable;
    /**
     * 标准输出日志
     */
    private boolean stdlog = true;
    /**
     * 忽略未知属性
     */
    private boolean ignoredUnknownField = true;

    private String schema;

    public static class Builder {
        private String driver = DRIVER_MYSQL;
        private String username = "root";
        private String password = "admin";
        private String schema;
        private String jdbcUrl = JDBC_URL_MYSQL;
        private int port = 3306;
        private String host = "localhost";
        private boolean underlineFiled = true;
        private boolean underlineTable = true;
        private boolean stdlog = true;
        private boolean ignoredUnknownField = true;

        public Builder(String schema) {
            this.schema = schema;
        }

        public Builder driver(String driver) {
            this.driver = driver;
            return this;
        }

        public Builder username(String username) {
            this.username = username;
            return this;
        }

        public Builder password(String password) {
            this.password = password;
            return this;
        }

        public Builder port(int port) {
            this.port = port;
            return this;
        }

        public Builder host(String host) {
            this.host = host;
            return this;
        }

        public Builder underlineField(boolean underlineFiled) {
            this.underlineFiled = underlineFiled;
            return this;
        }

        public Builder underlineTable(boolean underlineTable) {
            this.underlineTable = underlineTable;
            return this;
        }

        private Builder jdbcUrl(String jdbcUrl) {
            this.jdbcUrl = jdbcUrl;
            return this;
        }

        private Builder stdlog(boolean stdlog) {
            this.stdlog = stdlog;
            return this;
        }

        private Builder ignoreUnknownField(boolean ignoredUnknownField) {
            this.ignoredUnknownField = ignoredUnknownField;
            return this;
        }

        public DBUtils build() {
            String url = jdbcUrl.replace("{host}", host).replace("{port}", "" + port).replace("{schema}", schema);
            return new DBUtils(driver, schema, url, username, password, underlineFiled, underlineTable, stdlog, ignoredUnknownField);
        }
    }

    private DBUtils(String driver, String schema, String jdbcUrl, String username, String password, boolean underlineFiled, boolean underlineTable, boolean stdlog, boolean ignoredUnknownField) {
        this.driver = driver;
        this.schema = schema;
        this.jdbcUrl = jdbcUrl;
        this.username = username;
        this.password = password;
        this.underlineFiled = underlineFiled;
        this.underlineTable = underlineTable;
        this.stdlog = stdlog;
        this.ignoredUnknownField = ignoredUnknownField;

        initContext();
    }

    private void initContext() {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("初始化异常：" + this.toString());
        }
    }

    private void showInfo(String sql, Class<?> resultType, boolean underlineTable, boolean underlineFiled, Object... params) {
        showInfo(sql, (null == resultType ? "Not Required!" : resultType.getName()), underlineTable, underlineFiled, params);
    }

    private void showInfo(String sql, String resultType, boolean underlineTable, boolean underlineFiled, Object... params) {
        if (!stdlog) {
            return;
        }
        int index = sql.indexOf(" ");
        String sqlType = sql.substring(0, index).toUpperCase();
        System.out.println(">>> -------------------------------------------------------------------------------------------------");
        System.out.println(">>> Execute Info:\t" + sqlType);
        System.out.println(">>> ResultType: \t" + (null == resultType ? "Not Required!" : resultType));
        System.out.println(">>> underlineField:\t" + underlineFiled);
        System.out.println(">>> underlineTable:\t" + underlineTable);
        System.out.println(">>> SQL:\t\t\t" + sql);
        if (null != params && params.length > 0) {
            System.out.print(">>> Parameters: \t");
            StringBuilder builder = new StringBuilder();
            for (Object obj : params) {
                builder.append(null != obj ? obj.toString() + "" : "'null'").append(", ");
            }
            System.out.println(builder.toString().replaceFirst(", $", ""));
        } else {
            System.out.println(">>> Not Parameters!");
        }
        System.out.println(">>> -------------------------------------------------------------------------------------------------");
    }

    @Override
    public String toString() {
        return "DBUtils{" +
                "driver='" + driver + '\'' +
                ", jdbcUrl='" + jdbcUrl + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    public static Builder mysqlBuilder(String schema) {
        return new Builder(schema);
    }

    public static Builder oracleBuilder(String instance) {
        return new Builder(null == instance || "".equals(instance.trim()) ? "XE" : instance)
                .driver(DRIVER_ORACLE)
                .jdbcUrl(JDBC_URL_ORACLE)
                .port(1521)
                .username("SCOTT")
                .password("TIGER")
                ;
    }

    public DBUtils setStdlog(boolean stdlog) {
        this.stdlog = stdlog;
        return this;
    }

    public boolean isUnderlineFiled() {
        return underlineFiled;
    }

    public boolean isUnderlineTable() {
        return underlineTable;
    }

    public boolean isStdlog() {
        return stdlog;
    }

    public boolean isIgnoredUnknownField() {
        return ignoredUnknownField;
    }

    public void setIgnoredUnknownField(boolean ignoredUnknownField) {
        this.ignoredUnknownField = ignoredUnknownField;
    }

    private Connection openConnection() {
        try {
            return DriverManager.getConnection(jdbcUrl, username, password);
        } catch (SQLException e) {
            throw new RuntimeException("不能获取数据库链接");
        }
    }

    private DBUtils close(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return this;
    }

    public DBUtils close(Statement statement) {
        if (null != statement) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return this;
    }

    private DBUtils close(ResultSet resultSet) {
        if (null != resultSet) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return this;
    }

    private DBUtils close(Connection conn, Statement statement, ResultSet resultSet) {
        close(conn);
        close(statement);
        close(resultSet);
        return this;
    }

    /**
     * 包裹。加上 ` 以防关键字
     *
     * @param attr 属性
     * @return
     */
    public static String wrapDBAttr(String attr) {
        return "`" + unwrapDBAttr(attr) + "`";
    }

    /**
     * 解包裹， 把  ` 去掉
     *
     * @param attr 属性
     * @return
     */
    public static String unwrapDBAttr(String attr) {
        return attr.replaceAll("^`*", "").replaceAll("`*$", "");
    }

    /**
     * 获取创表语句
     *
     * @param table 数据表名称
     * @return
     */
    public String getCreateTableSql(String table) {
        String sql = "show create table " + wrapDBAttr(table);

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            System.out.println(sql);
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString(2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, resultSet);
        }
        return null;
    }

    private static final String GET_SCHEMA_TABLES_SQL = "SELECT DISTINCT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ?";

    /**
     * 获取数据库的所有表名称
     *
     * @param schema 数据库名称
     * @return
     */
    public List<String> getSchemaTables(String schema) {
        String realSchema = null == schema || "".equals(schema.trim()) ? this.schema : schema;
        String sql = GET_SCHEMA_TABLES_SQL;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            System.out.println(sql);
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, realSchema);
            resultSet = pstmt.executeQuery();
            List<String> tableList = new ArrayList<String>();
            while (resultSet.next()) {
                tableList.add(resultSet.getString(1));
            }
            return tableList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, resultSet);
        }
        return new ArrayList<String>();
    }

    private static final String GET_TABLE_COLUMNS_SQL = "SELECT TABLE_SCHEMA `schema`, TABLE_NAME tablename, COLUMN_NAME column_name,COLUMN_KEY column_key, ORDINAL_POSITION `order`, COLUMN_DEFAULT default_value, CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END allow_null, DATA_TYPE data_type, NUMERIC_PRECISION `numeric_precision`, NUMERIC_SCALE numeric_scale, CHARACTER_SET_NAME encoding, COLUMN_TYPE column_type, EXTRA extra, COLUMN_COMMENT `comment` " +
            "FROM information_schema.COLUMNS " +
            "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? " +
            "ORDER BY `order` ASC";

    /**
     * 获取某个表的所有列
     *
     * @param table 数据库表
     * @return
     */
    public List<Column> getTableColumns(String table) {
        String sql = GET_TABLE_COLUMNS_SQL;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            System.out.println(sql);
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, schema);
            pstmt.setString(2, table);
            resultSet = pstmt.executeQuery();
            List<Column> columnList = new ArrayList<Column>();
            while (resultSet.next()) {
                columnList.add(toObject(Column.class, resultSet));
            }
            return columnList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, resultSet);
        }
        return new ArrayList<Column>();
    }

    private static final String GET_TABLE_SQL = "SELECT TABLE_SCHEMA `schema`, TABLE_NAME tablename, `ENGINE` `engine`, AUTO_INCREMENT auth_increment, TABLE_COMMENT `comment` " +
            "FROM information_schema.TABLES " +
            "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

    /**
     * 获取数据库表的基本信息
     * @param table 数据库表名
     * @return
     */
    public Table getTable(String table) {
        String sql = GET_TABLE_SQL;

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            System.out.println(sql);
            conn = openConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, schema);
            pstmt.setString(2, table);
            resultSet = pstmt.executeQuery();
            List<Column> columnList = new ArrayList<Column>();
            if (resultSet.next()) {
                return toObject(Table.class, resultSet);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, pstmt, resultSet);
        }
        return null;
    }

    private <T> T toObject(Class<T> requiredType, ResultSet rs) {
        try {
            List<Field> fields = ReflectUtils.getDeclaredFields(requiredType);
            if (null != fields && !fields.isEmpty()) {
                T result = requiredType.newInstance();
                for (Field field : fields) {
                    try {
                        ReflectUtils.setFieldValue(result, field, getColumnValue(field.getName(), rs));
                    } catch (SQLException e) {
                        if (!ignoredUnknownField) {
                            throw new RuntimeException("没有对应 [" + field.getName() + "] 的属性！");
                        }
                    }
                }
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取值
     *
     * @param javaFieldName java属性名称
     * @param resultSet     结果集
     * @return
     */
    private Object getColumnValue(String javaFieldName, ResultSet resultSet) throws SQLException {
        // 先按照下划线处理
        String dbFieldName = toDBFileName(javaFieldName, true);
        try {
            return resultSet.getObject(dbFieldName);
        } catch (SQLException e) {
            dbFieldName = toDBFileName(javaFieldName, false);
            return resultSet.getObject(dbFieldName);
        }
    }

    /**
     * 转成数据库字段名称
     *
     * @param javaFieldName  java属性名称
     * @param underlineFiled 是否使用下划线
     * @return
     */
    public static String toDBFileName(String javaFieldName, boolean underlineFiled) {
        if (underlineFiled) {
            // 以下划线分割
            return firstLetterToLowerCase(javaFieldName).replaceAll("([A-Z]+)", "_$1").toLowerCase();
        }
        return javaFieldName;
    }

    /**
     * 首字母小写
     */
    private static String firstLetterToLowerCase(String value) {
        return null != value && value.length() > 0 ? String.valueOf(value.charAt(0)).toLowerCase() + value.substring(1) : null;
    }

}
